import pandas as pd

# 读取Excel文件
df = pd.read_excel('无标题.xlsx', sheet_name='Sheet1')

# 获取第二列的数据
second_column = df.iloc[:, 1]

# 定义需要删除的值
delete_type_mapping = {
    626,589,588,587,586,578,547,545,535,524,522,511,490,431,417,361,332,207,277,211,211,209,297,296,264,255,253,217,210,99,206,207,215,71,160
}

# 定义一个函数来处理每个单元格的值
def filter_values(cell_value):
    if pd.isna(cell_value):
        return ''
    # 确保 cell_value 是字符串类型
    cell_value = str(cell_value)
    # 分割字符串并转换为整数
    values = [int(float(v)) for v in cell_value.split(',')]
    filtered_values = [v for v in values if v not in delete_type_mapping]
    return ','.join(map(str, filtered_values))

# 应用函数到第二列
df['Filtered_INVOICE_TYPE'] = second_column.apply(filter_values)

# 新增一列，内容是SQL语句
df['SQL'] = df.apply(lambda row: f"update company_invoice_info set INVOICE_TYPE = NULL where DEPT_ID = {row['dept_id']};"
                                if row['Filtered_INVOICE_TYPE'] == '' else
                                f"update company_invoice_info set INVOICE_TYPE = '{row['Filtered_INVOICE_TYPE']}' where DEPT_ID = {row['dept_id']};", axis=1)

# 如果需要保存到新的Excel文件
df.to_excel('filtered_无标题.xlsx', index=False)
